﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_BBS_GetAllBoard]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_BBS_GetAllBoard];
GO
CREATE PROCEDURE [dbo].[sproc_BBS_GetAllBoard]
AS
BEGIN
    set nocount on;

    SELECT
        a.board_id,
        a.board_name,
        a.board_description,
        catalog_id,board_type,
        (SELECT COUNT(*) 
                FROM uds_bbs_forumitem b
                WHERE b.board_id = a.board_id
        ) as formitems,
        (SELECT COUNT(*) 
                FROM uds_bbs_replay c
                WHERE c.item_id in (SELECT b.item_id 
                                FROM uds_bbs_forumitem b
                                WHERE b.board_id = a.board_id)
        ) as replays,
        e.item_id,
        e.title,
        e.[content],
        e.sender,
        e.send_time 
    FROM
        uds_bbs_board a LEFT OUTER JOIN
        (
            SELECT * 
            from uds_bbs_forumitem m 
            where COALESCE(last_replay_time,send_time) = 
                (select max(COALESCE(last_replay_time,send_time)) from uds_bbs_forumitem n where m.board_id =n.board_id)
        ) e
        ON a.board_id = e.board_id
END;
GO